package com.geekchique.runningbuddy.provider;

public class RouteDBSQL {

	public static final String ROUTE_POINT_TABLE = "routePoint";

	public static final String ROUTE_TABLE = "route";

	public static final String ROUTE_ID = "_id";
	public static final String ROUTE_SID = "__id";
	public static final String ROUTE_GEOLOCATION_ID = "_idGeoLocation";
	public static final String ROUTE_NAME = "name";
	public static final String ROUTE_CREATED_AT = "createdAt";
	public static final String ROUTE_MODIFIED_AT = "modifiedAt";

	public static final String ROUTEPOINT_POINT_ID = "_id";
	public static final String ROUTEPOINT_ROUTE_ID = "_routePointId";
	public static final String ROUTEPOINT_GEOLOCATION_ID = "_idGeoLocation";
	public static final String ROUTEPOINT_POINTNO = "_idRoute";
	public static final String ROUTEPOINT_DIRECTION = "_idDirection";
	public static final String ROUTEPOINT_ALGORITHM = "algorithm";
	public static final String ROUTEPOINT_CREATED_AT = "createdAt";
	public static final String ROUTEPOINT_MODIFIED_AT = "modifiedAt";

	public static String[] COLUMNS_IN_ROUTE_ENTRY = new String[] { ROUTE_ID,
			ROUTE_SID, ROUTE_NAME, ROUTE_GEOLOCATION_ID, ROUTE_CREATED_AT,
			ROUTE_MODIFIED_AT };

	public static String[] COLUMNS_IN_ROUTEPOINT_ENTRY = new String[] {
			ROUTEPOINT_POINT_ID, ROUTEPOINT_ROUTE_ID,
			ROUTEPOINT_GEOLOCATION_ID, ROUTEPOINT_POINTNO,
			ROUTEPOINT_DIRECTION, ROUTEPOINT_ALGORITHM, ROUTEPOINT_CREATED_AT,
			ROUTEPOINT_MODIFIED_AT };

	public static String insertRouteJogId(String jogId) {
		return "INSERT INTO route (_jogId) " + "VALUES ( \'" + jogId + "\');";
	}

	public static String selectJustEnteredRouteId(String jogId) {
		return "SELECT _id " + "FROM route " + "WHERE _jogId = " + jogId
				+ " ORDER BY _id DESC " + "LIMIT 1";
	}

	public static String selectJustEnteredRoutePointId(String routeId) {
		return "SELECT _id FROM routePoint WHERE _routeId = " + routeId
				+ " ORDER BY _id DESC LIMIT 1";
	}

	public static String insertRoutePoint(String pointNo, String _routeId,
			String latitude, String longitude) {
		return "INSERT INTO routePoint (pointNo, _routeId, latitude, longitude ) VALUES ( \'"
				+ pointNo
				+ "\',\'"
				+ _routeId
				+ "\',\'"
				+ latitude
				+ "\',\'"
				+ longitude + "\')";

	}

	public static final String GET_LAST_ROUTE_POINT_FROM_JOG = "SELECT time FROM time"
			+ "WHERE _routeId = ("
			+ "					SELECT _id FROM route "
			+ "					WHERE _joId = 1 "
			+ "			)"
			+ "			ORDER BY time DESC LIMIT 1;";

}
